insert overwrite table jms_dwd.dwd_wide_abnormal_detail_waybill_dt
select
      abnormal.waybill_no                         --运单号
     ,abnormal.reback_apply_time                  --退转件申请时间
     ,abnormal.reback_apply_network_code          --退转件申请网点编码
     ,abnormal.reback_apply_network_name          --退转件申请网点名称
     ,abnormal.reback_apply_type_code             --退转件申请类型编码
     ,abnormal.reback_apply_type_name             --退转件申请类型描述
     ,abnormal.reback_abnormal_reason             --退转件原因
     ,abnormal.reback_print_time                  --退转件打印时间
     ,abnormal.reback_print_network_code          --退转件打印网点编码
     ,abnormal.reback_print_network_name          --退转件打印网点名称
     ,abnormal.reback_status                      --退转件状态:1待审核  2.已审核 3.取消申请  4.驳回
     ,abnormal.is_reback                          --是否退转件，1：是，0：否
     ,abnormal.difficult_time                     --问题件扫描时间
     ,abnormal.difficult_site_code                --问题件扫描网点编码
     ,abnormal.difficult_site                     --问题件扫描网点名称
     ,abnormal.difficult_type_code                --问题件问题类型编码
     ,abnormal.difficult_type_desc                --问题件问题类型描述
     ,abnormal.difficult_abnormal_reason          --问题件问题原因描述
     ,abnormal.difficult_input_time               --问题件录入时间
     ,abnormal.is_difficult                       --是否问题件，1：是，0：否
     ,abnormal.end_piece_time                     --完结件完结时间
     ,abnormal.end_piece_site_code                --完结件完结网点编码
     ,abnormal.end_piece_site                     --完结件完结网点名称
     ,abnormal.end_piece_type_code                --完结件完结类型编码
     ,abnormal.end_piece_type_desc                --完结件完结类型描述
     ,abnormal.end_piece_source                   --完结件来源来源 1、问题件 2、质量工单 3、仲裁 4、客服工单
     ,abnormal.end_piece_update_time              --完结件更新时间
     ,abnormal.is_end_piece                       --是否完结件，1：是，0：否
     ,abnormal.intercept_time                     --拦截件拦截时间
     ,abnormal.intercept_site_code                --拦截件拦截网点编码
     ,abnormal.intercept_site                     --拦截件拦截网点名称
     ,abnormal.intercept_type_code                --拦截件拦截类型编码
     ,abnormal.intercept_type_desc                --拦截件拦截类型描述
     ,abnormal.intercept_cancal_time              --拦截件撤销时间
     ,abnormal.intercept_cancal_network_code      --拦截件撤销网点编码
     ,abnormal.intercept_cancal_network_name      --拦截件撤销网点名称
     ,abnormal.intercept_scan_time                --拦截件扫描时间
     ,abnormal.intercept_scan_network_code        --拦截件扫描网点编码
     ,abnormal.intercept_scan_network_name        --拦截件扫描网点名称
     ,abnormal.intercept_registration_time        --拦截件登记时间
     ,abnormal.intercept_status                   --拦截件拦截状态:1待拦截, 2已拦截, 3已撤销
     ,abnormal.is_intercept                       --是否拦截件，1：是，0：否
     ,way.waybill_source_code                     --运单来源code
     ,way.waybill_source_name                     --运单来源名称
     ,way.pick_network_code                       --寄件网点code
     ,way.pick_network_name                       --寄件网点名称
     ,abnormal.end_piece_input_time               --完结件录入时间
     ,abnormal.reback_old_terminal_dispatch_code  --退转件原三段码
     ,way.dt as dt                                --运单表分区日期
from (
    select
          waybill_no --运单号
         ,max(case when scan_type = 'reback' then scan_time           end) as reback_apply_time         --退转件申请时间
         ,max(case when scan_type = 'reback' then scan_site_code      end) as reback_apply_network_code --退转件申请网点编码
         ,max(case when scan_type = 'reback' then scan_site           end) as reback_apply_network_name --退转件申请网点名称
         ,max(case when scan_type = 'reback' then type_code           end) as reback_apply_type_code    --退转件申请类型编码
         ,max(case when scan_type = 'reback' then type_desc           end) as reback_apply_type_name    --退转件申请类型描述
         ,max(case when scan_type = 'reback' then abnormal_reason     end) as reback_abnormal_reason    --退转件原因
         ,max(case when scan_type = 'reback' then print_time          end) as reback_print_time         --退转件打印时间
         ,max(case when scan_type = 'reback' then print_network_code  end) as reback_print_network_code --退转件打印网点编码
         ,max(case when scan_type = 'reback' then print_network_name  end) as reback_print_network_name --退转件打印网点名称
         ,max(case when scan_type = 'reback' then status              end) as reback_status             --退转件状态:1待审核  2.已审核 3.取消申请  4.驳回
         ,max(case when scan_type = 'reback' then 1 else 0            end) as is_reback                 --是否退转件，1：是，0：否
         ,max(case when scan_type = 'difficult' then scan_time        end) as difficult_time            --问题件扫描时间
         ,max(case when scan_type = 'difficult' then scan_site_code   end) as difficult_site_code       --问题件扫描网点编码
         ,max(case when scan_type = 'difficult' then scan_site        end) as difficult_site            --问题件扫描网点名称
         ,max(case when scan_type = 'difficult' then type_code        end) as difficult_type_code       --问题件问题类型编码
         ,max(case when scan_type = 'difficult' then type_desc        end) as difficult_type_desc       --问题件问题类型描述
         ,max(case when scan_type = 'difficult' then abnormal_reason  end) as difficult_abnormal_reason --问题件问题原因描述
         ,max(case when scan_type = 'difficult' then input_time       end) as difficult_input_time      --问题件录入时间
         ,max(case when scan_type = 'difficult' then 1 else 0         end) as is_difficult              --是否问题件，1：是，0：否
         ,max(case when scan_type = 'end' then scan_time              end) as end_piece_time            --完结件完结时间
         ,max(case when scan_type = 'end' then scan_site_code         end) as end_piece_site_code       --完结件完结网点编码
         ,max(case when scan_type = 'end' then scan_site              end) as end_piece_site            --完结件完结网点名称
         ,max(case when scan_type = 'end' then type_code              end) as end_piece_type_code       --完结件完结类型编码
         ,max(case when scan_type = 'end' then type_desc              end) as end_piece_type_desc       --完结件完结类型描述
         ,max(case when scan_type = 'end' then end_piece_source       end) as end_piece_source          --完结件来源来源 1、问题件 2、质量工单 3、仲裁 4、客服工单
         ,max(case when scan_type = 'end' then end_piece_update_time  end) as end_piece_update_time     --完结件更新时间
         ,max(case when scan_type = 'end' then 1 else 0               end) as is_end_piece              --是否完结件，1：是，0：否
         ,max(case when scan_type = 'intercept' then scan_time                     end) as intercept_time                --拦截件拦截时间
         ,max(case when scan_type = 'intercept' then scan_site_code                end) as intercept_site_code           --拦截件拦截网点编码
         ,max(case when scan_type = 'intercept' then scan_site                     end) as intercept_site                --拦截件拦截网点名称
         ,max(case when scan_type = 'intercept' then type_code                     end) as intercept_type_code           --拦截件拦截类型编码
         ,max(case when scan_type = 'intercept' then type_desc                     end) as intercept_type_desc           --拦截件拦截类型描述
         ,max(case when scan_type = 'intercept' then intercept_cancal_time         end) as intercept_cancal_time         --拦截件撤销时间
         ,max(case when scan_type = 'intercept' then intercept_cancal_network_code end) as intercept_cancal_network_code --拦截件撤销网点编码
         ,max(case when scan_type = 'intercept' then intercept_cancal_network_name end) as intercept_cancal_network_name --拦截件撤销网点名称
         ,max(case when scan_type = 'intercept' then intercept_scan_time           end) as intercept_scan_time           --拦截件扫描时间
         ,max(case when scan_type = 'intercept' then intercept_scan_network_code   end) as intercept_scan_network_code   --拦截件扫描网点编码
         ,max(case when scan_type = 'intercept' then intercept_scan_network_name   end) as intercept_scan_network_name   --拦截件扫描网点名称
         ,max(case when scan_type = 'intercept' then intercept_registration_time   end) as intercept_registration_time   --拦截件登记时间
         ,max(case when scan_type = 'intercept' then status                        end) as intercept_status              --拦截件拦截状态:1待拦截, 2已拦截, 3已撤销
         ,max(case when scan_type = 'intercept' then 1 else 0                      end) as is_intercept                  --是否拦截件，1：是，0：否
         ,max(case when scan_type = 'end' then input_time end) as end_piece_input_time  --完结件录入时间
         ,max(case when scan_type = 'reback' then old_terminal_dispatch_code end) as reback_old_terminal_dispatch_code   --退转件原三段码
    from(
        select
            *,row_number() over (partition by waybill_no,scan_type order by scan_time desc) as rn
        from jms_dwd.dwd_wide_abnormal_union_waybill_dt
        where dt between date_sub('{{ execution_date | cst_ds }}',60) and '{{ execution_date | cst_ds }}'  --取60天
    ) a where a.rn = 1
    group by waybill_no
) abnormal
left join (
    select
          waybill_no
         ,waybill_source_code  --运单来源code
         ,waybill_source_name  --运单来源名称
         ,pick_network_code    --寄件网点code
         ,pick_network_name    --寄件网点名称
         ,dt
    from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt
    where dt between date_sub('{{ execution_date | cst_ds }}',60) and '{{ execution_date | cst_ds }}'
) way on abnormal.waybill_no = way.waybill_no
where way.waybill_no is not null
distribute by dt,pmod(hash(rand()),10);
 